/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import cn.com.taiji.sdk.model.comm.protocol.user.UserUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.VehicleUploadRequest;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.Account;
import com.wtkj.vo.dzzf.base.kfwd.AccountOpt;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.VehicleInfo;
import com.wttech.dzzf.process.model.JgTemp;
import com.wttech.dzzf.process.model.Temp;
import com.wttech.dzzf.process.model.user.Card;
import com.wttech.dzzf.process.model.user.User;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * 数据初始化Dao实现
 * Created by MaShibo on 2017/10/23.
 */
@Repository
public class DataInitilizingDao {

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@SuppressWarnings("unchecked")
	public List<Account> queryAccountsByFundAccountId(String fundAccountId) {
		final String sql =
				"SELECT A.*, T.TRADENO " +
						"  FROM AC_ACCOUNT A " +
						"  LEFT JOIN AC_ACCOUNTOPT T " +
						"    ON A.ACCOUNTID = T.ACCOUNTID " +
						"   AND T.OPTTYPE = 1010 " +
						" WHERE A.FUNDACCOUNTID = ?";

		return (List<Account>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new Account()), fundAccountId);
	}

	@SuppressWarnings("unchecked")
	public List<AccountOpt> queryAccountOptsByAccountId(String accountId) {
		final String sql =
				"SELECT * " +
						"  FROM AC_ACCOUNTOPT A " +
						" WHERE A.OPTTYPE = 1010 " +
						"   AND A.STATE = 1 " +
						"   AND A.ACCOUNTID = ? " +
						" ORDER BY A.OPTON DESC ";
		return (List<AccountOpt>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new AccountOpt()), accountId);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByAccountId(String accountId) {
		final String sql =
				"SELECT A.*, J.CHANNELID " +
						"  FROM AC_CARDINFO A " +
						"     LEFT JOIN JG_ORGANIZATION J ON A.CLIENTNO = J.CODE " +
						" WHERE A.ACCOUNTID = ? " +
						"   AND A.ECARDSTATUS IN (1, 2, 3) " +
						" ORDER BY A.OPTON DESC ";
		return (List<CardInfo>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), accountId);
	}

	@SuppressWarnings("unchecked")
	public List<VehicleInfo> queryVehicleInfosByVehInfo(String vehPlate, Integer vehColor) {
		final String sql =
				"SELECT * " +
						"  FROM AC_VEHICLEINFO A " +
						" WHERE A.VEHPLATE = ? " +
						"   AND A.VEHCOLOR = ?";
		return (List<VehicleInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new VehicleInfo()), vehPlate, vehColor);
	}

	// ~ 检测数据
	// ===============================================

	private static final String QUERY_BS_NUM = "SELECT COUNT(1) FROM %s ";

	public int queryTableNum(String tableName) {
		return testJdbcTemplate.queryForObject(String.format(QUERY_BS_NUM, tableName), Integer.class);
	}

	public int queryCardInfoUserIdNum() {
		return dzzfJdbcTemplate.queryForObject("SELECT COUNT(1) FROM AC_CARDINFO WHERE USERID IS NOT NULL", Integer.class);
	}

	//~ 数据初始化拆分
	// =====================================================

	private static final String BASE_SUB_QUERY =
			"SELECT A.*, F.OWNERTYPE, ROWNUM AS RM " +
					"  FROM AC_CARDINFO A " +
					"  JOIN AC_ACCOUNT T " +
					"    ON A.ACCOUNTID = T.ACCOUNTID " +
					"  JOIN AC_FUNDACCOUNT F " +
					"    ON T.FUNDACCOUNTID = F.FUNDACCOUNTID " +
					" WHERE A.ECARDSTATUS IN (1, 2, 3) " +
					"   AND A.USERID IS NOT NULL";
	private static final String PERIOD_ONE = BASE_SUB_QUERY + " AND A.OPTON < TO_DATE(?, 'YYYYMMDD') ";
	private static final String PERIOD_TWO = BASE_SUB_QUERY + " AND A.OPTON >= TO_DATE(?, 'YYYYMMDD')  AND A.OPTON < TO_DATE(?, 'YYYYMMDD')  ";

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryOnePeriod(int startRow, int endRow, String endOn) {
		String sql = "SELECT * FROM (" + PERIOD_ONE + ") WHERE RM>=? AND  RM <=? ";
		return (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), endOn, startRow, endRow);
	}

	public int queryOnePeriodNum(String endOn) {
		final String sql = "SELECT COUNT(1) FROM (" + PERIOD_ONE + ") ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class, endOn);
	}

	public int queryTwoPeriodNum(String startOn, String endOn) {
		final String sql = "SELECT COUNT(1) FROM (" + PERIOD_TWO + ")";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class, startOn, endOn);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryTwoPeriod(int startRow, int endRow, String startOn, String endOn) {
		String sql = "SELECT * FROM ( " +
				PERIOD_TWO +
				") WHERE " + " RM >= ? " + " AND RM <= ?";
		return (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), startOn, endOn, startRow, endRow);
	}

	public User queryUserInfo(String userId) {
		final String sql =
				"SELECT A.ID, " +
						"       1 AS USERTYPE, " +
						"       A.USERNAME, " +
						"       A.USERIDTYPE, " +
						"       A.USERIDNUM, " +
						"       A.TEL, " +
						"       A.ADDRESS, " +
						"       2 AS REGISTEREDTYPE, " +
						"       A.CHANNELID, " +
						"       TO_CHAR(A.REGISTEREDTIME, 'yyyy-MM-dd') || 'T' || " +
						"       TO_CHAR(A.REGISTEREDTIME, 'hh24:mi:ss') AS REGISTEREDTIME, " +
						"       A.STATUS, " +
						"       TO_CHAR(A.STATUSCHANGETIME, 'yyyy-MM-dd') || 'T' || " +
						"       TO_CHAR(A.STATUSCHANGETIME, 'hh24:mi:ss') AS STATUSCHANGETIME, " +
						"       1 AS OPERATION " +
						"  FROM KF_USERINFO A " +
						" WHERE A.ID = ?";
		List<User> users = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), userId);
		return CollectionUtils.isEmpty(users) ? null : users.get(0);
	}

	public User queryCompanyInfo(String userId) {
		final String sql =
				"SELECT A.ID, " +
						"       2 AS USERTYPE, " +
						"       A.USERNAME, " +
						"       A.USERIDTYPE, " +
						"       A.USERIDNUM, " +
						"       A.TEL, " +
						"       A.ADDRESS, " +
						"       2 AS REGISTEREDTYPE, " +
						"       A.CHANNELID, " +
						"       TO_CHAR(A.REGISTEREDTIME, 'yyyy-MM-dd') || 'T' || " +
						"       TO_CHAR(A.REGISTEREDTIME, 'hh24:mi:ss') AS REGISTEREDTIME, " +
						"       A.STATUS, " +
						"       TO_CHAR(A.STATUSCHANGETIME, 'yyyy-MM-dd') || 'T' || " +
						"       TO_CHAR(A.STATUSCHANGETIME, 'hh24:mi:ss') AS STATUSCHANGETIME, " +
						"       1 AS OPERATION,  A.AGENTNAME, " +
						"       A.AGENTIDTYPE, " +
						"       A.AGENTIDNUM " +
						"  FROM KF_COMPANYINFO A " +
						" WHERE A.ID = ?";
		List<User> users = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), userId);
		return CollectionUtils.isEmpty(users) ? null : users.get(0);
	}

	// ~拆分资金账户遍历填充数据
	// ===============================================================

	public int queryFundAccountTotalNum() {
		final String sql =
				"SELECT COUNT(1) " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.ACCOUNTSTATUS = 1 " +
						"   AND EXISTS (SELECT 1 " +
						"          FROM AC_CARDINFO C " +
						"          JOIN AC_ACCOUNT T " +
						"            ON C.ACCOUNTID = T.ACCOUNTID " +
						"         WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID" +
						"      AND C.ECARDSTATUS IN (1,2,3)" +
						")  AND A.TOPCLASS = 6107 ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class);
	}

	public List<String> queryFundAccountIdByRow(int startRow, int endRow) {
		final String sql =
				"SELECT FUNDACCOUNTID " +
						"  FROM (SELECT A.FUNDACCOUNTID, ROWNUM AS RM " +
						"          FROM AC_FUNDACCOUNT A " +
						"         WHERE A.ACCOUNTSTATUS = 1 " +
						"           AND EXISTS (SELECT 1 " +
						"                  FROM AC_CARDINFO C " +
						"                  JOIN AC_ACCOUNT T " +
						"                    ON C.ACCOUNTID = T.ACCOUNTID " +
						"                 WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID" +
						"			AND C.ECARDSTATUS IN (1,2,3)" +
						") AND A.TOPCLASS = 6107 ) M " +
						" WHERE RM > = ? " +
						"   AND RM <= ?";
		return dzzfJdbcTemplate.query(sql, (rs, rowNum) -> rs.getString("FUNDACCOUNTID"), startRow, endRow);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByFundAccountId(String fundAccountId) {
		final String sql =
				"SELECT C.*, F.OWNERTYPE, F.FUNDACCOUNTID, J.CHANNELID " +
						"  FROM AC_CARDINFO C " +
						"  JOIN AC_ACCOUNT T " +
						"    ON C.ACCOUNTID = T.ACCOUNTID " +
						"  JOIN AC_FUNDACCOUNT F " +
						"    ON T.FUNDACCOUNTID = F.FUNDACCOUNTID " +
						"  LEFT JOIN JG_ORGANIZATION J " +
						"    ON C.CLIENTNO = J.CODE " +
						" WHERE C.ECARDSTATUS IN (1, 2, 3) " +
						"   AND F.FUNDACCOUNTID = ?";

		return (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), fundAccountId);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByIds() {
		final String sql =
				"\n" +
						"SELECT A.*, J.CHANNELID, 0 AS ownerType \n" +
						"  FROM AC_CARDINFO A\n" +
						"  LEFT JOIN JG_ORGANIZATION J\n" +
						"    ON A.CLIENTNO = J.CODE\n" +
						" WHERE A.CARDINFOID IN\n" +
						"       ('DCA33EB4-55D8-447F-E2B6-3D4F3EC9877B',\n" +
						"        '1448E072-D918-3D2C-1F58-514BF1CCAF01',\n" +
						"        'C1AR3D5D-CCBS-HIWY-CB05-000001018443',\n" +
						"        'C1AR3D5D-CCBS-HIWY-CB05-000000995281')";
		return (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()));
	}

	public int updateOrg(String name, String code) {
		final String sql = "UPDATE JG_ORGANIZATION A SET A.NAME = ? WHERE A.CODE=?";
		return dzzfJdbcTemplate.update(sql, name, code);
	}

	public int updateOrg(Temp temp) {
		final String sql = "UPDATE JG_ORGANIZATION A SET A.NAME=?, A.DIRECTOR=?,A.OFFICEPHONE=?,A.ADDRESS=? WHERE A.CODE=?";
		return dzzfJdbcTemplate.update(sql, temp.getName(), temp.getContact(), temp.getTel(), temp.getAddress(), temp.getCode());
	}

	public int updateOrg(JgTemp temp){
		final String sql = "UPDATE JG_ORGANIZATION A SET A.NAME=?, A.DIRECTOR=?,A.OFFICEPHONE=?,A.ADDRESS=? WHERE A.CODE=?";
		return dzzfJdbcTemplate.update(sql, temp.getName(), temp.getDirector(), temp.getTel(), temp.getAddress(), temp.getCode());
	}

	public int queryCardInfoNum() {
		final String sql = "SELECT COUNT(1) FROM BS_CARD";
		return testJdbcTemplate.queryForObject(sql, Integer.class);
	}

	@SuppressWarnings("unchecked")
	public List<Card> queryCardInfosByRow(int startRow, int endRow) {
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.ID, " +
						"               A.USERID, " +
						"                A.VEHICLEID, " +
						"               ROWNUM AS RM\n" +
						"          FROM BS_CARD A)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Card.class), startRow, endRow);
	}

	@SuppressWarnings("unchecked")
	public CardInfo queryCardInfoByNo(String ecardNo) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.ECARDNO=? ";
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), ecardNo);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}

	public int updateCardInfoUserId(String cardId, String userId) {
		final String sql = "UPDATE BS_CARD A SET A.USERID=? WHERE A.ID = ?";
		return testJdbcTemplate.update(sql, userId, cardId);
	}

	public int updateVehicleUserId(String vehicleId, String userId) {
		final String sql = "UPDATE BS_VEHICLE A SET A.USERID=? WHERE A.ID=?";
		return testJdbcTemplate.update(sql, userId, vehicleId);
	}

	public int updateUserIdByOldUserId(String userId, String oldUserId, String vehicleId){
		final String sql = "UPDATE BS_VEHICLE A SET A.USERID=? WHERE A.USERID=?  AND A.ID=? ";
		return testJdbcTemplate.update(sql, userId, oldUserId, vehicleId);
	}

	public int updateObuUserId(String userId, String oldUserId){
		final String sql = "UPDATE BS_OBU A SET A.USERID= ? WHERE A.USERID=? ";
		return testJdbcTemplate.update(sql, userId, oldUserId);
	}

	public List<String> queryCardIds(){
		final String sql =
				"SELECT ID\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM\n" +
						"          FROM BS_CARD A\n" +
						"         WHERE A.USERID NOT IN (SELECT ID FROM BS_USER))\n" +
						" WHERE RM >= 1\n" +
						"   AND RM <= 10000 ";
		return testJdbcTemplate.query(sql, (rs, rowNum) -> rs.getString("ID"));
	}

	public UserUploadRequest queryUserById(String id){
		final String sql = "SELECT * FROM BS_USER A WHERE A.ID=?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserUploadRequest.class), id).get(0);
	}

	public List<VehicleUploadRequest> queryVehicleByUserId(String userId){
		final String sql = "SELECT * FROM BS_VEHICLE A WHERE A.USERID=?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(VehicleUploadRequest.class), userId);
	}

	public int updateBsCardType(String id, Integer cardType){
		final String sql = "UPDATE BS_CARD A SET A.CARDTYPE=? WHERE A.ID=?";
		return  testJdbcTemplate.update(sql, cardType, id);
	}
}
